﻿package org.hyy.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.hyy.base.DatabaseConnection;
import org.hyy.vo.Category;
import org.hyy.vo.Product;

public class ProductDAO {
	private DatabaseConnection dbc;
	private Connection con;				//数据库连接对象
	private PreparedStatement ps;		//预编译对象，用来执行SQL语句
	private ResultSet rs;
	
	/**
	 * 增加商品
	 * @param product
	 * @return
	 */
	public int add(Product product){
		int i = 0;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "insert into product values (null, ?, ?, ?, ?, ?, ?)";
		
		try {
			ps = con.prepareStatement(sql);
			
			//设置参数
			ps.setString(1, product.getName());
			ps.setString(2, product.getDescr());
			ps.setDouble(3, product.getNormalPrice());
			ps.setDouble(4, product.getMemberPrice());
			ps.setTimestamp(5, new Timestamp(product.getPdate().getTime()));
			ps.setInt(6, product.getCategoryId());
			
			i = ps.executeUpdate();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return i;
	}
	
	/**
	 * 根据商品id删除商品
	 * @param id
	 * @return删除的行数
	 */
	public int delet(int id){
		int i = 0;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "delete from product where id = ?";
		try {
			ps = con.prepareStatement(sql);
			
			//设置参数
			ps.setInt(1, id);
			
			//执行语句
			i = ps.executeUpdate();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		
		return i;
	}
	
	/**
	 * 查询产品，按时间排序
	 * @return
	 */
	public List<Product> getProduct(){
		List<Product> products =new ArrayList<Product>();
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from product order by pdate desc";
		try {
			ps = con.prepareStatement(sql);
			
			//执行语句
			rs = ps.executeQuery();
			//存储所查结果
			while(rs.next()){
				Product product = new Product();
				product = getProductInfo(rs);
				products.add(product);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return products;
	}
	
	/**
	 * 封装从结果集中得到的结果
	 * @param rs
	 * @return
	 */
	private Product getProductInfo(ResultSet rs){
		Product p = null;
		p = new Product();
		try {
			p.setId(rs.getInt("id"));
			p.setName(rs.getString("name"));
			p.setDescr(rs.getString("descr"));
			p.setNormalPrice(rs.getDouble("normalprice"));
			p.setMemberPrice(rs.getDouble("memberprice"));
			p.setPdate(rs.getTimestamp("pdate"));
			p.setCategoryId(rs.getInt("categoryid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return p;
	}
	
	/**
	 * 取出记录用于分页显示
	 * if_word为真就不取类别
	 * @param products
	 * @param pageNo
	 * @param pageSize
	 * @param if_word
	 * @return
	 */
	public int getRecoder(List<Product> products,int pageNo,int pageSize,boolean if_word){
		int allRecoder = -1;
		
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		
		String sql = "";
		if(if_word){
			sql = "select * from product order by pdate desc";
		}else{
			sql = "select p.id productid, p.name pname, p.descr pdescr, p.normalprice, " +
			" p.memberprice, p.pdate, p.categoryid , " +
			" c.id categoryid, c.name cname, c.descr cdescr, c.pid, c.cno, c.grade " +
			" from product p join category c on (p.categoryid = c.id) order by p.pdate desc";
		}
		sql +=  " limit " + (pageNo - 1) * pageSize + "," + pageSize;
		//productid->category
		
		Statement stmtCount = null;
		ResultSet rsCount = null;
		
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			stmtCount = con.createStatement();
			rsCount = stmtCount.executeQuery(sql);
			if(rsCount.next()){
				allRecoder = rsCount.getInt(1);
			}
			
			while(rs.next()){
				Product p = null;
				if(if_word){
					p = this.getProductInfo(rs);
				}else{
					p = new Product();
					p.setId(rs.getInt("productid"));
					p.setName(rs.getString("pname"));
					p.setDescr(rs.getString("pdescr"));
					p.setNormalPrice(rs.getDouble("normalprice"));
					p.setMemberPrice(rs.getDouble("memberprice"));
					p.setPdate(rs.getTimestamp("pdate"));
					p.setCategoryId(rs.getInt("categoryid"));
					
					Category c = new Category();
					c.setId(rs.getInt("categoryid"));
					c.setName(rs.getString("cname"));
					c.setDescr(rs.getString("cdescr"));
					c.setPid(rs.getInt("pid"));
					c.setCno(rs.getInt("cno"));
					c.setGrade(rs.getInt("grade"));
					
					p.setCategory(c);
				}
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rsCount.close();
				stmtCount.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbc.close(con);
			dbc = null;
			
		}
		
		
		return allRecoder;
	}
	
	/**
	 * 通过商品ID查找商品
	 * @param id
	 * @return
	 */
	public Product getProductById(int id){
		Product product = null;
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from product where id = ?";
		try {
			ps = con.prepareStatement(sql);
			
			//设置参数
			ps.setInt(1, id);
			
			//执行语句
			rs = ps.executeQuery();
			//存储所查结果
			if(rs.next()){
				product = new Product();
				product = getProductInfo(rs);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return product;
	}
	
	/**
	 * 查询产品，根据分类ID
	 * @return
	 */
	public List<Product> getProductByCid(int cid){
		List<Product> products =new ArrayList<Product>();
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from product where categoryid=? order by pdate desc";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, cid);
			//执行语句
			rs = ps.executeQuery();
			//存储所查结果
			while(rs.next()){
				Product product = new Product();
				product = getProductInfo(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return products;
	}
	
	/**
	 * 新品展示
	 */
	public List<Product> getTopTenProducts(){
		List<Product> products =new ArrayList<Product>();
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from product order by pdate desc limit 10";
		try {
			ps = con.prepareStatement(sql);
			//执行语句
			rs = ps.executeQuery();
			//存储所查结果
			while(rs.next()){
				Product product = new Product();
				product = getProductInfo(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return products;
	}
	
	/**
	 * 每个类别中的前5个
	 */
	public List<Product> getTopFiveProducts(int cid){
		List<Product> products =new ArrayList<Product>();
		//打开数据库
		dbc = new DatabaseConnection();
		con = dbc.getCon();
		//操作数据库
		String sql = "select * from product where categoryid=? order by pdate desc limit 5";
		try {
			ps = con.prepareStatement(sql);
			//执行语句
			ps.setInt(1, cid);
			rs = ps.executeQuery();
			//存储所查结果
			while(rs.next()){
				Product product = new Product();
				product = getProductInfo(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			//关闭数据库
			dbc.close(con);
			dbc = null;
		}
		return products;
	}
}
